package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;
import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.P2pYwyDao;

import net.sf.json.JSONObject;

@Repository
public class P2pYwyDaoImpl extends P2PJdbcDao implements P2pYwyDao{

	Logger logger = Logger.getLogger(this.getClass());
	
	public List<Map<String, Object>>  queryYwysstjList(JSONObject params){
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String TJ_START = params.optString("TJ_START");
		String TJ_END = params.optString("TJ_END");
		String TJR = params.optString("TJR");
		StringBuffer sql = new StringBuffer();
		Map<String,Object> paramMap = new HashMap<>();
		paramMap.put("TJ_START", TJ_START);
		paramMap.put("TJ_END", TJ_END);
		paramMap.put("TJR", TJR);
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append(" select tt1.TJR, tt1.ct TJR_CT, tt2.s_tzje TJR_S_TZJE ");
		sql.append(" from                                                                                                         ");
		sql.append(" (                                                                                                            ");
		sql.append("  select tjr,count(*) ct from t_zhfw_user                                                                     ");
		sql.append(" 	where tjr is not null and tjr <>'' and DISABLED =1                                                        ");
		if(StringUtils.isNotEmpty(TJ_START)){
			sql.append("     and date_format(cjrq,'%Y-%m-%d') >= :TJ_START 			");
		}
		if(StringUtils.isNotEmpty(TJ_END)){
			sql.append("	 and date_format(cjrq,'%Y-%m-%d') <= :TJ_END			");
		}
		sql.append("     group by tjr order by ct desc                                                                            ");
		sql.append(" )tt1 left join                                                                                               ");
		sql.append(" (                                                                                                            ");
		sql.append(" 	select t2.tjr, sum(tcje) s_tzje from t_zhfw_tzrxx t1, t_zhfw_user t2 where t1.USERID = t2.USER_NAME       ");
		sql.append(" 	 and t2.tjr is not null and t2.tjr <> ''  and t2.DISABLED =1                                          ");
		if(StringUtils.isNotEmpty(TJ_START)){
			sql.append("     and date_format(t1.TZSJ,'%Y-%m-%d') >= :TJ_START ");
		}
		if(StringUtils.isNotEmpty(TJ_END)){
			sql.append("	 and date_format(t1.TZSJ,'%Y-%m-%d') <= :TJ_END ");
		}
		sql.append(" 	group by t2.tjr                                                                                           ");
		sql.append(" )tt2 on tt1.tjr = tt2.tjr ");
		if(StringUtils.isNotEmpty(TJR)){
			sql.append(" WHERE tt1.tjr = :TJR ");
		}
		if(limit !=0 ){
			sql.append(" limit :START,:LIMIT ");
		}
		logger.info("queryYwysstjList sql is : " + sql.toString());
		logger.info("queryYwysstjList paramMap is : " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}
	
	public int getYwysstjCount(JSONObject params){
		String TJ_START = params.optString("TJ_START");
		String TJ_END = params.optString("TJ_END");
		String TJR = params.optString("TJR");
		StringBuffer sql = new StringBuffer();
		Map<String,Object> paramMap = new HashMap<>();
		paramMap.put("TJ_START", TJ_START);
		paramMap.put("TJ_END", TJ_END);
		paramMap.put("TJR", TJR);
		sql.append(" select COUNT(*) CT ");
		sql.append(" from                                                                                                         ");
		sql.append(" (                                                                                                            ");
		sql.append("  select tjr,count(*) ct from t_zhfw_user                                                                     ");
		sql.append(" 	where tjr is not null and tjr <>'' and DISABLED =1                                                        ");
		if(StringUtils.isNotEmpty(TJ_START)){
			sql.append("     and date_format(cjrq,'%Y-%m-%d') >= :TJ_START 			");
		}
		if(StringUtils.isNotEmpty(TJ_END)){
			sql.append("	 and date_format(cjrq,'%Y-%m-%d') <= :TJ_END			");
		}
		sql.append("     group by tjr order by ct desc                                                                            ");
		sql.append(" )tt1 left join                                                                                               ");
		sql.append(" (                                                                                                            ");
		sql.append(" 	select t2.tjr, sum(tcje) s_tzje from t_zhfw_tzrxx t1, t_zhfw_user t2 where t1.USERID = t2.USER_NAME       ");
		sql.append(" 	 and t2.tjr is not null and t2.tjr <> ''  and t2.DISABLED =1                                          ");
		if(StringUtils.isNotEmpty(TJ_START)){
			sql.append("     and date_format(t1.TZSJ,'%Y-%m-%d') >= :TJ_START ");
		}
		if(StringUtils.isNotEmpty(TJ_END)){
			sql.append("	 and date_format(t1.TZSJ,'%Y-%m-%d') <= :TJ_END ");
		}
		sql.append(" 	group by t2.tjr                                                                                           ");
		sql.append(" )tt2 on tt1.tjr = tt2.tjr ");
		if(StringUtils.isNotEmpty(TJR)){
			sql.append(" WHERE tt1.tjr = :TJR ");
		}
		logger.info("getYwysstjCount sql is : " + sql.toString());
		logger.info("getYwysstjCount paramMap is : " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}
}
